const pool = require('./db');

class Order {
   async create(orderData) {
    const { room_id, customer_id, total_price } = orderData;
    let {start_time, end_time} = orderData
    start_time = new Date(start_time).toISOString().slice(0, 19).replace('T', ' '); 
    end_time = new Date(end_time).toISOString().slice(0, 19).replace('T', ' '); 
    const [result] = await pool.query(
      'INSERT INTO `order` (room_id, customer_id, start_time, end_time, total_price, order_status, create_time) VALUES (?, ?, ?, ?, ?, "unpaid", NOW())',
      [room_id, customer_id, start_time, end_time, total_price]
    );
    return result.insertId;
  }

   async findById(orderId) {
    const [rows] = await pool.query('SELECT * FROM `order` WHERE order_id = ?', [orderId]);
    return rows[0];
  }

  async findByRoomId(room_id) {
    const rows = await pool.query('SELECT * FROM `order` WHERE room_id = ? ',[room_id])
    return rows[0]
  }
   async findByCustomer(customerId, status = null) {
    let query = 'SELECT o.*, r.room_name FROM `order` o JOIN conference_room r ON o.room_id = r.room_id WHERE o.customer_id = ?';
    const params = [customerId];
    
    if (status) {
      query += ' AND o.order_status = ?';
      params.push(status);
    }
    
    const [rows] = await pool.query(query, params);
    return rows;
  }

   async cancel(orderId) {
    await pool.query(
      'UPDATE `order` SET order_status = "canceled", cancel_time = NOW(), refund_status = "pending" WHERE order_id = ?',
      [orderId]
    );
  }

   async pay(orderId) {
    await pool.query(
      'UPDATE `order` SET order_status = "paid" WHERE order_id = ?',
      [orderId]
    );
  }

   async updateRefundStatus(orderId, status) {
    await pool.query(
      'UPDATE `order` SET refund_status = ? WHERE order_id = ?',
      [status, orderId]
    );
  }

   async checkRoomAvailability(roomId, startTime, endTime) {
    const [rows] = await pool.query(
      'SELECT * FROM `order` WHERE room_id = ? AND ' +
      '((start_time < ? AND end_time > ?) OR ' +
      '(start_time < ? AND end_time > ?) OR ' +
      '(start_time >= ? AND end_time <= ?))',
      [roomId, endTime, startTime, endTime, startTime, startTime, endTime]
    );
    return rows.length === 0;
  }

   async autoReleaseLockedRooms() {
    // 释放30分钟未支付的订单
    const [result] = await pool.query(
      'UPDATE `order` o JOIN conference_room r ON o.room_id = r.room_id ' +
      'SET r.status = "idle", o.order_status = "canceled" ' +
      'WHERE o.order_status = "unpaid" AND TIMESTAMPDIFF(MINUTE, o.create_time, NOW()) > 30'
    );
    return result.affectedRows;
  }
}

module.exports = new Order();